const { Sequelize } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: '888mei525',
  database: 'lingxi_system',
  logging: false
});

async function createIncomingInvoicesTable() {
  try {
    await sequelize.authenticate();
    console.log('数据库连接成功');
    
    // 检查表是否已存在
    const [results, metadata] = await sequelize.query('SHOW TABLES LIKE "incoming_invoices"');
    if (results.length > 0) {
      console.log('incoming_invoices表已存在');
      return;
    }
    
    // 创建进项发票表
    const createTableSQL = `
      CREATE TABLE incoming_invoices (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
        organization_id INT NOT NULL COMMENT '组织ID',
        supplier_id INT COMMENT '供应商ID',
        invoice_number VARCHAR(50) NOT NULL UNIQUE COMMENT '发票号码',
        invoice_code VARCHAR(50) COMMENT '发票代码',
        invoice_type VARCHAR(20) DEFAULT 'general' COMMENT '发票类型',
        invoice_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '发票金额',
        tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '税额',
        deductible_tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '可抵扣税额',
        invoice_date DATE NOT NULL COMMENT '开票日期',
        receive_date DATE COMMENT '收票日期',
        certification_status ENUM('uncertified', 'certified', 'failed') DEFAULT 'uncertified' COMMENT '认证状态',
        certification_date DATE COMMENT '认证日期',
        invoice_status ENUM('received', 'certified', 'deducted', 'archived') DEFAULT 'received' COMMENT '发票状态',
        payment_status ENUM('unpaid', 'partial_paid', 'paid') DEFAULT 'unpaid' COMMENT '付款状态',
        due_date DATE COMMENT '到期日期',
        remarks TEXT COMMENT '备注',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_organization_id (organization_id),
        INDEX idx_supplier_id (supplier_id),
        INDEX idx_invoice_number (invoice_number),
        INDEX idx_invoice_date (invoice_date),
        INDEX idx_certification_status (certification_status),
        INDEX idx_invoice_status (invoice_status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='进项发票表';
    `;
    
    await sequelize.query(createTableSQL);
    console.log('成功创建incoming_invoices表');
    
  } catch (error) {
    console.error('创建表时出错:', error);
  } finally {
    await sequelize.close();
  }
}

createIncomingInvoicesTable();